import pandas as pd
import numpy as np

# 读取CSV文件
df = pd.read_csv('分类表数据.csv')

# 生成创建表的SQL语句
def generate_create_table_sql(df, table_name='category'):
    # 定义SQL数据类型映射
    sql_types = {
        'id': 'INT PRIMARY KEY',
        'name': 'VARCHAR(255) NOT NULL',
        'parent_id': 'INT NOT NULL',
        'level': 'INT NOT NULL',
        'sort': 'INT NOT NULL',
        'is_enabled': 'TINYINT NOT NULL',
        'url': 'VARCHAR(255)',
        'create_time': 'DATETIME NOT NULL',
        'update_time': 'DATETIME NOT NULL'
    }
    
    # 构建CREATE TABLE语句
    columns = []
    for column in df.columns:
        if column in sql_types:
            columns.append(f"`{column}` {sql_types[column]}")
        else:
            # 默认使用VARCHAR类型
            columns.append(f"`{column}` VARCHAR(255)")
    
    create_table_sql = f"CREATE TABLE IF NOT EXISTS `{table_name}` (\n"
    create_table_sql += ",\n".join(["  " + col for col in columns])
    create_table_sql += "\n);"
    
    return create_table_sql

# 生成批量插入数据的SQL语句
def generate_insert_sql(df, table_name='category', batch_size=1000):
    # 获取列名
    columns = df.columns.tolist()
    columns_str = ",".join([f"`{col}`" for col in columns])
    
    # 构建INSERT语句
    insert_sqls = []
    rows = []
    
    for _, row in df.iterrows():
        # 处理每一行数据
        values = []
        for col in columns:
            value = row[col]
            if pd.isna(value):
                values.append('NULL')
            elif isinstance(value, str):
                # 转义字符串中的单引号
                escaped_value = value.replace("'", "''")
                values.append(f"'{escaped_value}'")
            else:
                values.append(str(value))
        
        rows.append("(" + ",".join(values) + ")")
        
        # 达到批量大小或处理完所有数据时生成INSERT语句
        if len(rows) >= batch_size or _ == len(df) - 1:
            insert_sql = f"INSERT INTO `{table_name}` ({columns_str}) VALUES\n"
            insert_sql += ",\n".join(rows)
            insert_sql += ";"
            insert_sqls.append(insert_sql)
            rows = []
    
    return insert_sqls

# 执行生成SQL语句
create_table_sql = generate_create_table_sql(df)
insert_sqls = generate_insert_sql(df)

# 保存SQL语句到文件
with open('category.sql', 'w', encoding='utf-8') as f:
    f.write(create_table_sql)
    f.write('\n\n')
    
    # 写入所有的INSERT语句
    for i, insert_sql in enumerate(insert_sqls):
        if i > 0:
            f.write('\n\n')
        f.write(insert_sql)

print("SQL语句生成完成！")
print(f"创建表语句已保存到：create_category_table.sql")
print(f"共生成了 {len(insert_sqls)} 个批量插入语句，覆盖了 {len(df)} 条数据。")